from build.lib.vnpy.trader.utility import load_json
from tsmirror_setting import token, db_url,cachefile_path
import tushare as ts
from warpper import Warpper, CachedManager
from mirrordb import MirrorDB
from datetime import date, timedelta

import requests
import pandas as pd
from sqlalchemy import create_engine
from typing import List, Dict, Any
import json
import os

class CashFlowCachedManager(CachedManager):

    def __init__(self,filename=None):
        if filename is None:
            filename = os.path.join(cachefile_path, "CacheFile", "cashflowcached.json")
        super().__init__()  # 调用父类的 __init__
        self.cacheflagfile=filename
        self.checkflags={}  #{‘000001.SZ：（20120101,20250206)}
        self.loadcacheflag()
        self.paramlist=['ts_code','startdate','enddate']
        self.paramdict_flag = False #代表paramdict是否有效
        self.new_paramdict = None #假如需要对中间数据补充，该字典则为新的时间范围

    def getenddate(self, paramdict):
        enddate = paramdict.get('enddate')
        if enddate is None:
            today = date.today()
            yesterday = today - timedelta(days=1)
            formatted_date = yesterday.strftime("%Y%m%d")
            enddate = int(formatted_date)

        return enddate

    def checkcacheflag(self, paramdict):
        ts_code = paramdict.get('ts_code')
        startdate = paramdict.get('startdate')
        enddate = self.getenddate(paramdict)

        if ts_code is None or startdate is None or enddate < startdate:  # 补充结束日期小于起始日期同样无效
            print("invalid parameters")
            return False

        self.paramdict_flag = True
        daterange = self.checkflags.get(ts_code)
        if daterange is not None:
            if (startdate >= daterange[0]) and (daterange[1] >= enddate):
                print("cache hit")
                return True
            else:
                print("daterange is out of scope")

        print(f"cache missed: {ts_code} is not in checkflags")
        return False

    def updatecacheflag(self, paramdict):
        ts_code = paramdict.get('ts_code')
        startdate = paramdict.get('startdate')
        enddate = self.getenddate(paramdict)

        if ts_code in self.checkflags.keys():
            # TODO: 根据现有的起止时间和新的起止时间 合并新的范围
            if self.paramdict_flag:
                daterange = self.checkflags.get(ts_code)

                # 特殊情况：假如两次调用tushare的时间范围离散，则系统会自动调用中间未被调用的数据保持数据连贯
                if startdate > daterange[1]:  # 需要对左间范围补充
                    self.new_paramdict = {'ts_code': ts_code, 'startdate': daterange[1], 'enddate': enddate}
                elif enddate < daterange[0]:  # 需要对右范围补充
                    self.new_paramdict = {'ts_code': ts_code, 'startdate': startdate, 'enddate': daterange[0]}

                # 拓展新的时间范围
                else:
                    self.new_paramdict = None
                    if startdate < daterange[0] or daterange[0] is None:
                        daterange[0] = startdate
                    if enddate > daterange[1] or daterange[1] is None:
                        daterange[1] = enddate
                    self.checkflags[ts_code] = daterange
                    self.savecacheflag()
            else:
                print("invalid parameters")
        else:
            self.checkflags[ts_code] = (startdate, enddate)
            self.savecacheflag()

    def loadcacheflag(self):
        # TODO: 从持久化的checkflag里面获取缓存信息
        try:
            if os.path.exists(self.cacheflagfile):
                print(f"File '{self.cacheflagfile}' exists.")
                self.checkflags = load_json(self.cacheflagfile)
        except PermissionError:
            print("Permission denied to access the file or directory.")

    def savecacheflag(self):
        # TODO: 将checkflag持久化
        with open(self.cacheflagfile, "w") as file:
            json.dump(self.checkflags, file)


class CashFlowWarpper(Warpper):

    def __init__(self):
        """
        Initialize the CashFlowWarpper class.
        """
        super().__init__()  # 调用父类的 __init__
        self.cache_manager = CashFlowCachedManager()
        self.db = MirrorDB(db_url)

    def __get_db_table(self, paramdict):
        ts_code = paramdict.get('ts_code')
        table_name = 'cashflow_' + ts_code.replace(".", "_").lower()  # replease 000001.SZ to cashflow_000001_sz
        return table_name

    def query_data(self, paramdict, forceupdate=False) -> bool:
        """
        Check if there are cached data.

        :param params: List of parameters .
        :renewflag:  if this is True, ignore cached data
        :return: True if get data. False if query data failed.
        """

        if not forceupdate and self.cache_manager.checkcacheflag(paramdict):
            df = self.read_from_db(paramdict)
        else:
            df = self.read_from_tushare(paramdict)

        return df

    def read_from_db(self, paramdict) -> pd.DataFrame:
        """
        Read data from a database table.

        :param paramsdict: parameter list for tushare cashflow().
        :return: DataFrame containing the query results.
        """
        ts_code = paramdict.get('ts_code')
        startdate = paramdict.get('startdate')
        enddate = self.cache_manager.getenddate(paramdict)

        table_name = self.__get_db_table(paramdict)

        query = f"SELECT * FROM {table_name} WHERE ann_date>={startdate} and ann_date<={enddate}"

        print(query)

        try:
            df = pd.read_sql(query, con=self.db.engine)
            print(f"Data successfully read from table '{table_name}'.")
            return df
        except Exception as e:
            print(f"Error reading from database: {e}")
            return pd.DataFrame()

    def write_to_db(self, paramdict) -> None:
        """
        Write data to a database table.

        :param table_name: Name of the table to write to.
        :param data: DataFrame containing the data to write.
        :param if_exists: Behavior if the table already exists ('replace', 'append', 'fail').
        """
        try:
            # TODO:写入数据库,更新缓存表

            table_name = self.__get_db_table(paramdict)  # 设置表名

            # 更新缓存表
            if self.cache_manager.checkcacheflag(paramdict) is False:
                self.cache_manager.updatecacheflag(paramdict)
                if self.cache_manager.new_paramdict is not None:
                    paramdict = self.cache_manager.new_paramdict
                    self.cache_manager.updatecacheflag(paramdict)

                df = self.read_from_tushare(paramdict)

                self.db.create_table_from_df(table_name, df)  # 如果表从来没建立过，就把tushare读取的数据建立成表
                self.db.df_to_db(table_name, df)
                print(f"Data successfully written to table '{table_name}'.")

        except Exception as e:
            print(f"Error writing to database: {e}")

    def read_from_tushare(self, paramdict) -> pd.DataFrame:
        """
        Fetch data from an online API.

        :param params: Optional parameters to pass to the API request.
        :return: DataFrame containing the fetched data.
        """
        ts_code = paramdict.get('ts_code')
        startdate = paramdict.get('startdate')
        enddate = self.cache_manager.getenddate(paramdict)

        print(ts_code, startdate, enddate)

        # pro = self.getproinstance()
        ts.set_token(token)
        pro = ts.pro_api()

        df = pro.cashflow(ts_code=ts_code, start_date=str(startdate), end_date=str(enddate))
        df = df.fillna("NODATA")
        return df

    def close(self):
        """
        Close the database connection.
        """
        self.engine.dispose()
        print("Database connection closed.")


# Example Usage
if __name__ == "__main__":
    # Database URL (replace with your credentials)
    # db_url = "mysql+pymysql://user:password@localhost:3306/test_db"

    # Initialize the Wrapper class
    wrapper = CashFlowWarpper()

    # Example 1: Check for duplicate parameters
    params = {'ts_code': '600000.SH', 'startdate': 20160101, 'enddate': 20190730}
    df = wrapper.read_from_tushare(params)  # 从tushare中读取数据


    df.to_csv("cashflow_warpper.csv")
    df['net_profit'] = df['net_profit'].fillna(0)
    print(df['net_profit'])
    wrapper.write_to_db(params)  # 将数据导入数据库并更新缓存

    print(df.head(5))
